package com.ferret.dao;

import com.ferret.bean.entity.ReadPic;
import com.ferret.bean.entity.RyGzryxxcj;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

public interface ReadPicDao extends JpaRepository<ReadPic, String>, JpaSpecificationExecutor<ReadPic> {

    @Query(value = "select * from (select rownum as rn,t.czsj,t.zp,t.djdw,t.glgzrybh,r.ryzt,r.gmsfhm from GZRY_ZPXX t,RY_GZRYXXCJ r where t.glgzrybh = r.gzrybh  and r.ryzt='1' ) where 1=1 and rn between :startRowIndex and :endRowIndex", nativeQuery = true)
    List<ReadPic> findAllPagedByOracle(@Param("startRowIndex") int startRowIndex, @Param("endRowIndex") int endRowIndex);

    @Query(value = "select t.czsj,t.djdw,t.glgzrybh from GZRY_ZPXX t,RY_GZRYXXCJ r  where (t.djdw like '4117%' or t.djdw like '4128%') and t.glgzrybh = r.gzrybh and r.ryzt='1'", nativeQuery = true)
    List<ReadPic> findTestDataList();

    @Query(value = "select count(1) from GZRY_ZPXX t,RY_GZRYXXCJ r  where (t.djdw like '4117%' or t.djdw like '4128%') and t.glgzrybh = r.gzrybh and r.ryzt='1'", nativeQuery = true)
    Long findTestDataCount();

    @Query(value = "select count(1) from GZRY_ZPXX t,RY_GZRYXXCJ r where t.glgzrybh = r.gzrybh and r.ryzt='1'", nativeQuery = true)
    Long findAllPagedByOracleCount();

    //  该sql为查询关注人员信息
    @Query(value = "select count(1) from GZRY_ZPXX t,RY_GZRYXXCJ r where t.glgzrybh = r.gzrybh and r.ryzt='1' and (t.djdw like '4117%' or t.djdw like '4128%') ", nativeQuery = true)
    Long findByAreaCounter();

    //该sql为查询关注人员信息
    @Query(value = "select * from (select rownum as rn,t.czsj,t.zp,t.djdw,t.glgzrybh,r.ryzt,r.gmsfhm from GZRY_ZPXX t,RY_GZRYXXCJ r where t.glgzrybh = r.gzrybh and r.ryzt='1' and (t.djdw like '4117%' or t.djdw like '4128%') ) where 1=1 and rn between :startRowIndex and :endRowIndex", nativeQuery = true)
    List<ReadPic> findByArea(@Param("startRowIndex") int startRowIndex, @Param("endRowIndex") int endRowIndex);
}
